var express = require("express");
var router = express.Router();
const fs = require("fs");
const xlsx = require("xlsx");
// const Token = require("./Token");
// const crypto = require("crypto");
// const Student = require("./model/Student");
// const Teacher = require("./model/Teacher");

// const Courses = require("./model/Courses");
const Scores = require("./model/Scores");

// 处理文件用
const moment = require("moment");
var mutipart = require("connect-multiparty");
var mutipartMiddeware = mutipart();
var file_uploadDir = __dirname + "/public/xlsx/"; // 保存文件相对目录
//设置保存目录
router.use(
  mutipart({
    uploadDir: file_uploadDir
  })
);
router.get("/w", async function(req, res) {
  res.json({
    msg: "欢迎使用just文接口"
  });
});
//上传docx格式题库
router.post("/uploadExecl", mutipartMiddeware, async function(req, res) {
  //这里打印可以看到接收到文件的信息。
  var { term, grade, classes, exam, subject } = req.body;
  console.log(grade);
  console.log(classes);
  console.log(subject);
  var uploadedPath = req.files.file.path;
  var timestr = moment()
    .utcOffset(8)
    .format()
    .substring(0, 10);
  timestr = timestr.replace(new RegExp("-", "gm"), "_");
  var fileName = term + grade + classes + exam + subject + ".xlsx";
  var filePath = file_uploadDir + timestr + fileName;
  //重命名为真实文件名
  fs.rename(uploadedPath, filePath, async function(err) {
    if (err) {
      console.log(timestr + "：上传失败！" + err);

      res.json({
        code: 404,
        msg: "上传失败！"
      });
    } else {
      console.log(filePath + "：上传成功!");
      try {
        await Excel_ScoresInsert(filePath);
        res.json({
          code: 200,
          msg: "上传成功！"
        });
      } catch (err) {
        res.json({
          code: 400,
          msg: err
        });
      }
    }
  });
});

// 成绩——xlsx表格插入数据库
async function Excel_ScoresInsert(filepath) {
  //读取内容
  const workbook = xlsx.readFile(filepath);
  const sheetNames = workbook.SheetNames; //获取表名
  const sheet = workbook.Sheets[sheetNames[0]]; //通过表名得到表对象
  const thead = [
    sheet.A1.v,
    sheet.B1.v,
    sheet.C1.v,
    sheet.D1.v,
    sheet.E1.v,
    sheet.F1.v,
    sheet.G1.v,
    sheet.H1.v,
    sheet.I1.v,
    sheet.J1.v
  ];
  const data = xlsx.utils.sheet_to_json(sheet); //通过工具将表对象的数据读出来并转成json
  const theadRule = [
    "序号",
    "学期",
    "年级",
    "班级",
    "考试",
    "科目",
    "学号",
    "姓名",
    "成绩",
    "备注"
  ];
  const isValid = thead.every((value, index) => value === theadRule[index]); //检验表字段
  if (!isValid) {
    console.log("校验不正确！");
    return false;
  }

  let params = [];
  for (let i = 0; i < data.length; i++) {
    params.push({
      num: data[i][thead[0]],
      term: data[i][thead[1]],
      grade: data[i][thead[2]],
      classes: data[i][thead[3]],
      exam: data[i][thead[4]],
      subject: data[i][thead[5]],
      sNum: data[i][thead[6]],
      sName: data[i][thead[7]],
      scores: data[i][thead[8]],
      msg: data[i][thead[9]]
    });
  }
  // console.log(params);
  var scores = await Scores.bulkCreate(params);
  console.log(scores);
}

module.exports = router;
